{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、Pandas的一维序列结构：Series\n",
    "\n",
    "- Series是定长的字典序列。在存储的时候，相当于两个ndarray，这是和字典结构最大的不同。字典的结构里，元素个数不固定。\n",
    "- Series两个基本属性：index和values。index默认是0,1,2...，也可自定义：index=[‘a’,‘b’,‘c’,‘d’]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "x1 = Series([1,2,3,4]) # 采用默认值定义\n",
    "x2 = Series(data=[1,2,3,4], index=['a', 'b', 'c', 'd']) # 自定义指定\n",
    "print (x1)\n",
    "print (x2)\n",
    "d = {'a':1, 'b':2, 'c':3, 'd':4} # 也可以用字典的方式来创建\n",
    "x3 = Series(d)\n",
    "print (x3) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、Pandas的二维表结构：DataFrame\n",
    "\n",
    "- 类似数据库表，可以将DataFrame看成是由相同索引的Series组成的字典类型。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 虚构一个王者荣耀考试的场景，输出几位英雄的考试成绩：\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "data = {'Chinese': [66, 95, 93, 90,80],'English': [65, 85, 92, 88, 90],'Math': [30, 98, 96, 77, 90]}\n",
    "df1= DataFrame(data)\n",
    "df2 = DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei'], columns=['English', 'Math', 'Chinese'])\n",
    "print (df1)\n",
    "print (df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、Series 和 DataFrame的使用方法：数据导入和输出、数据清洗、数据统计、数据表合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1、数据导入和输出\n",
    "\n",
    "- Pandas 允许直接从 xlsx，csv 等文件中导入数据，也可以输出到 xlsx, csv 等文件，非常方便。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 运行的过程可能会存在缺少 xlrd 和 openpyxl 包的情况，可以在命令行模式下使用“pip install”命令来进行安装。\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "score = DataFrame(pd.read_excel('data.xlsx'))\n",
    "score.to_excel('data1.xlsx')\n",
    "print (score)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2、数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "# 以上面王者荣耀的数据为例\n",
    "data = {'Chinese': [66, 95, 93, 90,80],'English': [65, 85, 92, 88, 90],'Math': [30, 98, 96, 77, 90]}\n",
    "df2 = DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei'], columns=['English', 'Math', 'Chinese'])\n",
    "print (data)\n",
    "print (df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.1、在数据清洗过程中，一般都会遇到几种情况：\n",
    "\n",
    "1. 删除 DataFrame 中的不必要的列或行\n",
    "2. 重命名列名 columns，让列表名更容易识别\n",
    "3. 去重复的值\n",
    "4. 格式问题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "data = {'Chinese': [66, 95, 93, 90,80],'English': [65, 85, 92, 88, 90],'Math': [30, 98, 96, 77, 90]}\n",
    "df2 = DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei'], \n",
    "                columns=['English', 'Math', 'Chinese'])\n",
    "# 1. 删除 DataFrame 中的不必要的列或行\n",
    "df2 = df2.drop(columns=['Chinese'])  # 删除“语文”这一行\n",
    "df2 = df2.drop(index=['ZhangFei'])  # 删除“张飞”这一行\n",
    "\n",
    "# 2. 重命名列名 columns，让列表名更容易识别\n",
    "# ______列名Chinese改成YuWen，English改成YingYu\n",
    "df2.rename(columns={'Chinese': 'YuWen', 'English': 'Yingyu'}, inplace = True)\n",
    "\n",
    "# 3. 去重复的值\n",
    "df2 = df2.drop_duplicates() #去除重复行\n",
    "\n",
    "# 4. 格式问题\n",
    "# ______更改数据格式\n",
    "df = df2['Chinese'].astype('str') # 把 Chinese 字段的值改成 str 类型，或者 int64\n",
    "df = df2['Chinese'].astype(np.int64)\n",
    "# ______数据间的空格(不知为何这三句运行失败)\n",
    "df2['Chinese']=df2['Chinese'].map(str.strip) # 删除左右两边空格\n",
    "df2['Chinese']=df2['Chinese'].map(str.lstrip) # 删除左边空格\n",
    "df2['Chinese']=df2['Chinese'].map(str.rstrip) # 删除右边空格\n",
    "# __________如果数据里有某个特殊的符号(不知为何这一句运行失败)\n",
    "df2['Chinese']=df2['Chinese'].str.strip('$') # 删除Chinese字段里的美元符号\n",
    "# ______大小写转换\n",
    "df2.columns = df2.columns.str.upper() # 全部大写\n",
    "df2.columns = df2.columns.str.lower() # 全部小写\n",
    "df2.columns = df2.columns.str.title() # 首字母大写\n",
    "# ______查找空值\n",
    "# __________想看下哪个地方存在空值 NaN，可以针对数据表 df 进行 df.isnull()\n",
    "# __________想知道哪列存在空值，可以使用 df.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.2、使用 apply 函数对数据进行清洗："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 这一段代码跳过(暂不运行)\n",
    "# 对 name 列的数值都进行大写转化\n",
    "df['name'] = df['name'].apply(str.upper)\n",
    "# 定义 double_df 函数是将原来的数值 *2 进行返回。然后对 df1 中的“语文”列的数值进行 *2 处理\n",
    "def double_df(x):\n",
    "           return 2*x\n",
    "df1[u'语文'] = df1[u'语文'].apply(double_df)\n",
    "# 定义更复杂的函数，比如对于 DataFrame，新增两列，其中’new1’列是“语文”和“英语”成绩之和的 m 倍，\n",
    "# 'new2’列是“语文”和“英语”成绩之和的 n 倍\n",
    "def plus(df,n,m):\n",
    "    df['new1'] = (df[u'语文']+df[u'英语']) * m\n",
    "    df['new2'] = (df[u'语文']+df[u'英语']) * n\n",
    "    return df\n",
    "df1 = df1.apply(plus,axis=1,args=(2,3,))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3、数据统计\n",
    "\n",
    "在数据清洗后，就要对数据进行统计，如果遇到空值 NaN，会自动排除\n",
    "统计函数：\n",
    "    1. count()  统计个数，空值NaN不计算\n",
    "    2. describe()  一次性输出多个指标\n",
    "    3. min()、max()、sum()、mean()  最大、小值，总和，平均值\n",
    "    4. median()、var()、std()  中位数，方差，标准差\n",
    "    5. argmin()、argmax()  统计最小、大值的索引位置\n",
    "    6. idxmin()、idxmax()  统计最小、大值的索引值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一次性输出多个指标\n",
    "df1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})\n",
    "print (df1.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4、数据表合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建两个 DataFrame：\n",
    "df1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})\n",
    "df2 = DataFrame({'name':['ZhangFei', 'GuanYu', 'A', 'B', 'C'], 'data2':range(5)})\n",
    "\n",
    "# 两个 DataFrame 数据表的合并使用的是 merge() 函数，有下面 5 种形式：\n",
    "\n",
    "# 1. 基于指定列进行连接\n",
    "df3 = pd.merge(df1, df2, on='name') # 基于 name 进行连接\n",
    "# 2. inner 内连接\n",
    "#      inner 内链接是 merge 合并的默认情况，inner 内连接其实也就是键的交集\n",
    "#      f1, df2 相同的键是 name，所以是基于 name 字段的连接\n",
    "df3 = pd.merge(df1, df2, how='inner')\n",
    "# 3. left 左连接\n",
    "#      以第一个 DataFrame 为主进行的连接，第二个 DataFrame 作为补充\n",
    "df3 = pd.merge(df1, df2, how='left')\n",
    "# 4. right 右连接\n",
    "#      右连接是以第二个 DataFrame 为主进行的连接，第一个 DataFrame 作为补充\n",
    "df3 = pd.merge(df1, df2, how='right')\n",
    "# 5. outer 外连接\n",
    "#      外连接相当于求两个 DataFrame 的并集\n",
    "df3 = pd.merge(df1, df2, how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、如何用 SQL 方式打开 Pandas (引入工具:pandasql)\n",
    "\n",
    "- pandasql 中的主要函数是 sqldf，接收两个参数：一个 SQL 查询语句，一组环境变量 globals() 或 locals()。可直接用 SQL 语句中对 DataFrame 进行操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "from pandasql import sqldf, load_meat, load_births\n",
    "df1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})\n",
    "print(df1)\n",
    "pysqldf = lambda sql: sqldf(sql, globals())\n",
    "sql = \"select * from df1 where name ='ZhangFei'\"\n",
    "print (pysqldf(sql))\n",
    "\n",
    "# 常看到lambda，lambda是用来定义一个匿名函数的\n",
    "# argument_list 是参数列表，expression 是关于参数的表达式，会根据 expression 表达式计算结果进行输出返回\n",
    "lambda argument_list: expression\n",
    "# 例如：sql是输入参数；返回的结果是 sqldf 对 sql 的运行结果；\n",
    "pysqldf = lambda sql: sqldf(sql, globals()) # globals 全局参数，因为在 sql 中有对全局参数 df1 的使用\n",
    "print(pysqldf)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
